﻿Imports System.Data.OleDb
Public Class ChiTietSuDungTheDAO
    Shared conn_str As String = My.Settings.ConnectionString
    Shared Function GetAll(ByVal MaUser As Integer) As DataTable
        Dim dt As New DataTable
        Dim query As String = "select ChiTietSuDungThe.MaTheTinDung,sum(ChiTietSuDungThe.SoTien) as No," & _
        "(sum(ChiTietSuDungThe.SoTien) - TongSoTienDaTra) ConLai," & _
        "TongSoTienDaTra," & _
        "YEAR(ChiTietSuDungThe.NgaySuDung) namsudung,MONTH(ChiTietSuDungThe.NgaySuDung) thangsudung from ChiTietSuDungThe " & _
        "inner join TheTinDung on TheTinDung.MaTheTinDung = ChiTietSuDungThe.MaTheTinDung " & _
        "left join (select mathetindung,nam,thang,SUM(sotien) TongSoTienDaTra from QuanLyThuChi.dbo.ChiTietTraTinDung " & _
        "group by MaTheTinDung,nam,thang) c on c.MaTheTinDung = ChiTietSuDungThe.MaTheTinDung and MONTH(ChiTietSuDungThe.ngaysudung) = c.thang and YEAR(ChiTietSuDungThe.ngaysudung) = c.nam " & _
        "where MaUser = ? " & _
        "group by ChiTietSuDungThe.MaTheTinDung,YEAR(ChiTietSuDungThe.NgaySuDung),MONTH(ChiTietSuDungThe.NgaySuDung),TongSoTienDaTra"
        Try
            Dim adapter As New OleDbDataAdapter(query, conn_str)
            adapter.SelectCommand.Parameters.Add("@MaUser", OleDbType.Integer, 11).Value = MaUser
            adapter.FillSchema(dt, SchemaType.Source)
            adapter.Fill(dt)
        Catch Loi As CustomException.MyException
            Loi.Show(query, conn_str)
        End Try
        Return dt
    End Function
    Shared Function GetAll(ByVal MaTheTinDung As String) As DataTable
        Dim dt As New DataTable
        Dim query As String = "select * from ChiTietSuDungThe where MaTheTinDung = ?"
        Try
            Dim adapter As New OleDbDataAdapter(query, conn_str)
            adapter.SelectCommand.Parameters.Add("@MaTheTinDung", OleDbType.VarChar, 20).Value = MaTheTinDung
            adapter.FillSchema(dt, SchemaType.Source)
            adapter.Fill(dt)
        Catch Loi As CustomException.MyException
            Loi.Show(query, conn_str)
        End Try
        Return dt
    End Function

End Class
